package demo01;

import java.sql.*;

public class JdbcCrudExample {

    // 数据库连接信息
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/test2?useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "mysql";

    /**
     * 连接数据库的公共方法
     *
     * @return Connection对象
     * @throws SQLException
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
    }

    /**
     * C - Create (插入数据)
     *
     * @param name 用户名
     * @param age  用户年龄
     */
    public static void createUser(int id, String name, String password, String address) {
        String sql = "INSERT INTO user (id, name,password,address) VALUES (?, ?,?,?)";
        // 使用 try-with-resources 自动关闭资源
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, null); // 设置第一个参数，对应 ?
            pstmt.setString(2, name);      // 设置第二个参数，对应 ?
            pstmt.setString(3, password);
            pstmt.setString(4, address);

            int rowsAffected = pstmt.executeUpdate();
            System.out.println("成功创建用户: " + name + ", 影响行数: " + rowsAffected);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * R - Read (查询所有数据)
     */
    public static void readAllUsers() {
        String sql = "SELECT * FROM user";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) { // 查询操作使用 executeQuery

            System.out.println("\n--- 用户列表 ---");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String password = rs.getString("password");
                String address = rs.getString("address");
                System.out.println("ID: " + id + ", 名称: " + name + ", 密码: " + password + ", 地址: " + address);
            }
            System.out.println("-----------------");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * U - Update (更新数据)
     *
     * @param id      用户ID
     * @param newName 新的用户名
     */
    public static void updateUser(int id, String newName) {
        String sql = "UPDATE user SET name = ? WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newName);
            pstmt.setInt(2, id);
            int rowsAffected = pstmt.executeUpdate();
            System.out.println("成功更新用户ID为 " + id + " 的用户，影响行数: " + rowsAffected);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * D - Delete (删除数据)
     *
     * @param id 用户ID
     */
    public static void deleteUser(int id) {
        String sql = "DELETE FROM user WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            int rowsAffected = pstmt.executeUpdate();
            System.out.println("成功删除用户ID为 " + id + " 的用户，影响行数: " + rowsAffected);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        createUser(1, "张三", "123456", "北京");
        readAllUsers();
        // updateUser(1, "张三1");
        // readAllUsers();
        // deleteUser(1);
        // readAllUsers();
        // System.out.println("程序结束");
    }
}